﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using System.Data;
using Data.Attributes;
using Data;

namespace BusinessLayer.Tables
{
    public class MasterTable
    {
        public String GetInsert_SQLStrings()
        {
            Type tip = this.GetType();
            TablesAttribute tblAtr = ((TablesAttribute[])tip.GetCustomAttributes(typeof(TablesAttribute), false))[0];
            string tablename = tblAtr.Table_Name;
            StringBuilder insertBuilder = new StringBuilder();
            insertBuilder.Append("Insert into ");
            insertBuilder.Append(tablename);
            insertBuilder.Append(" (");
            foreach (PropertyInfo prp in tip.GetProperties())
            {
                String abc = prp.Name.ToString();
                FieldsAttribute atr = ((FieldsAttribute[])prp.GetCustomAttributes(typeof(FieldsAttribute), false))[0];
                string fieldname = atr.Fields_Name;
                if (atr.InInsert && prp.GetValue(this, null) != null)
                    insertBuilder.Append(fieldname + ",");
            }
            insertBuilder.Remove(insertBuilder.Length - 1, 1);
            insertBuilder.Append(") Values (");
            foreach (PropertyInfo prp in tip.GetProperties())
            {
                FieldsAttribute atr = ((FieldsAttribute[])prp.GetCustomAttributes(typeof(FieldsAttribute), false))[0];
                if (atr.InInsert && prp.GetValue(this, null) != null)
                    insertBuilder.Append(Factory.GetFactory.GetParameterToken() + atr.Fields_Name + ",");
            }
            insertBuilder.Remove(insertBuilder.Length - 1, 1);
            insertBuilder.Append(")");
            return insertBuilder.ToString();
        }

        public String GetInsert_SQLComands()
        {
            Type tip = this.GetType();
            TablesAttribute tblAtr = ((TablesAttribute[])tip.GetCustomAttributes(typeof(TablesAttribute), false))[0];
            string tablename = tblAtr.Table_Name;
            StringBuilder insertBuilder = new StringBuilder();
            insertBuilder.Append("Insert into ");
            insertBuilder.Append(tablename);
            insertBuilder.Append(" (");


            foreach (PropertyInfo prp in tip.GetProperties())
            {
                String abc = prp.Name.ToString();
                FieldsAttribute atr = ((FieldsAttribute[])prp.GetCustomAttributes(typeof(FieldsAttribute), false))[0];

                string fieldname = atr.Fields_Name;
                if (atr.InInsert && prp.GetValue(this, null) != null)
                    insertBuilder.Append(fieldname + ",");
            }

            insertBuilder.Remove(insertBuilder.Length - 1, 1);
            insertBuilder.Append(") Values (");


            foreach (PropertyInfo prp in tip.GetProperties())
            {
                FieldsAttribute atr = ((FieldsAttribute[])prp.GetCustomAttributes(typeof(FieldsAttribute), false))[0];
                if (!atr.Identity)
                {
                    object alanDegeri = prp.GetValue(this, null);
                    if ((prp.PropertyType.Name == "String")
                            || (prp.PropertyType.Name == "DateTime"))
                        insertBuilder.Append("'" + prp.GetValue(this, null).ToString() + "',");
                    else
                        insertBuilder.Append(prp.GetValue(this, null).ToString() + ",");
                }
            }

            insertBuilder.Remove(insertBuilder.Length - 1, 1);
            insertBuilder.Append(")");
            return insertBuilder.ToString();
        }

        public Boolean CreateTable()
        {
            if (TableExists() == false)
            {
                Boolean sonuc = true;
                try
                {
                    Type tip = this.GetType();
                    TablesAttribute tblAtr = ((TablesAttribute[])tip.GetCustomAttributes(typeof(TablesAttribute), false))[0];
                    string tablename = tblAtr.Table_Name;
                    String _table = Create_table_sql();
                    Factory.GetFactory.ExecuteNonQuery(_table);
                    String _constraints = Create_TableConstraint();
                    if (_constraints != "")
                    {
                        Factory.GetFactory.ExecuteNonQuery(_constraints);
                    }

                    //Stack<Int16> stk = new Stack<Int16>();
                    Int16 idx = 0;
                    //foreach (PropertyInfo prp in tip.GetProperties())
                    //{
                    //    Data.Attributes.FieldsAttribute atr = ((Data.Attributes.FieldsAttribute[])prp.GetCustomAttributes(typeof(Data.Attributes.FieldsAttribute), false))[0];
                    //    if (stk.Contains(atr.IndexGroup1) == false && atr.IndexGroup1 > -1)
                    //    {
                    //        stk.Push(atr.IndexGroup1);

                    //    }
                    //}
                    for (int i = 0; i < 5; i++)
                    {
                        String index_sql = CrateTableIndex(i, idx);
                        idx += 1;
                        if (index_sql != null && index_sql != "")
                            Factory.GetFactory.ExecuteNonQuery(index_sql);

                    }
                    sonuc = true;
                }
                catch (Exception exp)
                {
                    sonuc = false;
                    //ErrorHandler.ErrorHand(exp);
                }
                return sonuc;
            }
            else
            {
                if (ColumnExists() == false)
                {
                    ColumnRepair();
                }
                return true;
            }
        }

        public String CrateTableIndex(int indexgroup, Int16 idx)
        {
            Type tip = this.GetType();
            TablesAttribute tblAtr = ((TablesAttribute[])tip.GetCustomAttributes(typeof(TablesAttribute), false))[0];
            string tablename = tblAtr.Table_Name;
            StringBuilder createBuilder = new StringBuilder();
            createBuilder.Append("CREATE INDEX ");
            createBuilder.Append(" IDX_" + tablename + "_" + idx.ToString() + " ON ");
            createBuilder.Append(tablename + "(");

            Boolean ilk = true;
            Boolean girdi = false;

            foreach (PropertyInfo prp in tip.GetProperties())
            {
                String abc = prp.Name.ToString();
                FieldsAttribute atr = ((FieldsAttribute[])prp.GetCustomAttributes(typeof(FieldsAttribute), false))[0];
                if (ilk == true && atr.UniqueIndex == true)
                {
                    ilk = false;
                    createBuilder.Replace("CREATE INDEX", "CREATE UNIQUE INDEX");
                }

                string fieldname = atr.Fields_Name;
                if (atr.Indexed == true && (atr.IndexGroup1 == indexgroup || atr.IndexGroup2 == indexgroup || atr.IndexGroup3 == indexgroup || atr.IndexGroup4 == indexgroup || atr.IndexGroup5 == indexgroup))
                {
                    createBuilder.Append(fieldname + ",");
                    girdi = true;
                }
            }

            createBuilder.Remove(createBuilder.Length - 1, 1);
            createBuilder.Append(")");
            if (girdi == false)
            {
                createBuilder.Remove(0, createBuilder.Length);
            }
            return createBuilder.ToString();

        }

        public bool ColumnExists()
        {
            Type tip = this.GetType();
            TablesAttribute tblAtr = ((TablesAttribute[])tip.GetCustomAttributes(typeof(TablesAttribute), false))[0];
            string tablename = tblAtr.Table_Name;
            String sql = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='" + tablename + "'";
            IDataReader rd = Factory.GetFactory.ExecuteReader(sql);
            rd.Read();
            Int32 kolon_sayisi = 0;
            kolon_sayisi = rd.GetInt32(0);
            rd.Close();
            rd.Dispose();
            int class_kolon = tip.GetProperties().Length;
            bool sonuc = true;
            if (class_kolon != kolon_sayisi)
                sonuc = false;
            return sonuc;
        }

        public String Create_TableConstraint()
        {
            Type tip = this.GetType();
            TablesAttribute tblAtr = ((TablesAttribute[])tip.GetCustomAttributes(typeof(TablesAttribute), false))[0];
            string tablename = tblAtr.Table_Name;
            StringBuilder createBuilder = new StringBuilder();
            createBuilder.Append("ALTER TABLE ");
            createBuilder.Append(tablename);
            createBuilder.Append(" ADD CONSTRAINT PK_" + tablename + " PRIMARY KEY ( ");
            bool var = false;
            foreach (PropertyInfo prp in tip.GetProperties())
            {
                String abc = prp.Name.ToString();
                FieldsAttribute atr = ((FieldsAttribute[])prp.GetCustomAttributes(typeof(FieldsAttribute), false))[0];

                string fieldname = atr.Fields_Name;
                if (atr.PrimaryKey == true)
                {
                    var = true;
                    createBuilder.Append(fieldname + ","); //alan adı koyuyor
                }

            }

            createBuilder.Remove(createBuilder.Length - 1, 1);
            createBuilder.Append(")");

            if (var == false)
            {
                createBuilder.Remove(0, createBuilder.Length);
            }
            return createBuilder.ToString();
        }

        public string GetDbtype(SqlDbType type, Int32 len, Boolean identity)
        {
            if (identity == false)
                if (type == SqlDbType.NVarChar || type == SqlDbType.VarChar)
                    return type.ToString() + "(" + len.ToString() + ")";
                else
                    return type.ToString();
            else
            {
                return type.ToString() + " IDENTITY ";
            }
        }

        public String Create_table_sql()
        {
            Type tip = this.GetType();
            TablesAttribute tblAtr = ((TablesAttribute[])tip.GetCustomAttributes(typeof(TablesAttribute), false))[0];
            string tablename = tblAtr.Table_Name;
            StringBuilder createBuilder = new StringBuilder();
            createBuilder.Append("CREATE TABLE ");
            createBuilder.Append(tablename);
            createBuilder.Append(" (");

            foreach (PropertyInfo prp in tip.GetProperties())
            {
                String abc = prp.Name.ToString();
                FieldsAttribute atr = ((FieldsAttribute[])prp.GetCustomAttributes(typeof(FieldsAttribute), false))[0];

                string fieldname = atr.Fields_Name;
                createBuilder.Append(fieldname + " " + GetDbtype(atr.DbType, atr.Size, atr.Identity));
                if (atr.IsNullable == true)
                    createBuilder.Append(" NULL,");
                else
                    createBuilder.Append(" NOT NULL,");
            }

            createBuilder.Remove(createBuilder.Length - 1, 1);
            createBuilder.Append(")");

            return createBuilder.ToString();
        }

        public void ColumnRepair()
        {
            Type tip = this.GetType();
            TablesAttribute tblAtr = ((TablesAttribute[])tip.GetCustomAttributes(typeof(TablesAttribute), false))[0];
            string tablename = tblAtr.Table_Name;
            String sql = "SELECT COLUMN_NAME,DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME='" + tablename + "'";
            IDataReader rd = Factory.GetFactory.ExecuteReader(sql);
            Dictionary<string, string> lst = new Dictionary<string, string>();
            string ad = "";
            string dbtip = "";
            while (rd.Read())
            {
                ad = rd.GetString(0);
                dbtip = rd.GetString(1);
                lst.Add(ad, dbtip);
            }
            foreach (PropertyInfo prp in tip.GetProperties())
            {
                FieldsAttribute atr = ((FieldsAttribute[])prp.GetCustomAttributes(typeof(FieldsAttribute), false))[0];
                string fieldname = atr.Fields_Name;
                if (lst.ContainsKey(fieldname) == false)
                {
                    String txtsql = "Alter Table " + tablename + " ADD " + fieldname + " " + GetDbtype(atr.DbType, atr.Size, atr.Identity);
                    Factory.GetFactory.ExecuteNonQuery(txtsql);
                }
            }
            rd.Close();
            rd.Dispose();
        }

        public Boolean TableExists()
        {
            Type tip = this.GetType();
            TablesAttribute tblAtr = ((TablesAttribute[])tip.GetCustomAttributes(typeof(TablesAttribute), false))[0];
            string tablename = tblAtr.Table_Name;
            String sql = "SELECT COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='" + tablename + "'";
            IDataReader rd = Factory.GetFactory.ExecuteReader(sql);
            rd.Read();
            bool sonuc = false;
            if (rd.GetInt32(0) > 0)
            {
                sonuc = true;
            }
            rd.Close();
            rd.Dispose();
            return sonuc;
        }

        public String GetAllRecord_Sql()
        {
            Type tip = this.GetType();
            TablesAttribute tblAtr = ((TablesAttribute[])tip.GetCustomAttributes(typeof(TablesAttribute), false))[0];
            string tablename = tblAtr.Table_Name;
            StringBuilder selectBuilder = new StringBuilder();
            selectBuilder.Append("SELECT ");
            foreach (PropertyInfo prp in tip.GetProperties())
            {
                FieldsAttribute atr = ((FieldsAttribute[])prp.GetCustomAttributes(typeof(FieldsAttribute), false))[0];

                string fieldname = atr.Fields_Name;
                if (atr.InSelect == true)
                {
                    selectBuilder.Append(fieldname + ",");
                }
            }
            selectBuilder.Remove(selectBuilder.Length - 1, 1);
            selectBuilder.Append(" FROM " + tablename);
            return selectBuilder.ToString();
        }

        public DataTable GetAllRecord_Datatable()
        {
            Type tip = this.GetType();
            TablesAttribute tblAtr = ((TablesAttribute[])tip.GetCustomAttributes(typeof(TablesAttribute), false))[0];
            string tablename = tblAtr.Table_Name;
            StringBuilder selectBuilder = new StringBuilder();
            selectBuilder.Append("SELECT ");

            foreach (PropertyInfo prp in tip.GetProperties())
            {
                FieldsAttribute atr = ((FieldsAttribute[])prp.GetCustomAttributes(typeof(FieldsAttribute), false))[0];

                string fieldname = atr.Fields_Name;
                if (atr.InSelect == true)
                {
                    selectBuilder.Append(fieldname + ","); //alan adı koyuyor
                }
            }

            selectBuilder.Remove(selectBuilder.Length - 1, 1);
            selectBuilder.Append(" FROM " + tablename);

            DataTable dt;
            dt = Factory.GetFactory.ExecuteDataTable(selectBuilder.ToString());
            return dt;
        }
        public DataTable GetAllRecord_Datatable2(string sqlQuery)
        {
            DataTable dt;
            dt = Factory.GetFactory.ExecuteDataTable(sqlQuery);
            return dt;
        }
        public DataTable GetAllRecord_Datatable(params String[] select)
        {
            Type tip = this.GetType();
            TablesAttribute tblAtr = ((TablesAttribute[])tip.GetCustomAttributes(typeof(TablesAttribute), false))[0];
            string tablename = tblAtr.Table_Name;
            StringBuilder selectBuilder = new StringBuilder();
            selectBuilder.Append("SELECT ");

            for (int i = 0; i < select.Length; i++)
            {
                selectBuilder.Append(select[i] + ",");
            }
            selectBuilder.Remove(selectBuilder.Length - 1, 1);
            selectBuilder.Append(" FROM " + tablename);

            DataTable dt;
            dt = Factory.GetFactory.ExecuteDataTable(selectBuilder.ToString());
            return dt;
        }
        public DataTable GetAllRecord_Datatable(string[] select, params String[] where)
        {
            Type tip = this.GetType();
            TablesAttribute tblAtr = ((TablesAttribute[])tip.GetCustomAttributes(typeof(TablesAttribute), false))[0];
            string tablename = tblAtr.Table_Name;
            StringBuilder selectBuilder = new StringBuilder();
            selectBuilder.Append("SELECT ");

            for (int i = 0; i < select.Length; i++)
            {
                selectBuilder.Append(select[i] + ",");
            }
            selectBuilder.Remove(selectBuilder.Length - 1, 1);
            selectBuilder.Append(" FROM " + tablename + " WHERE ");

            foreach (String prp in where)
            {
                selectBuilder.Append(prp + " and ");
            }
            selectBuilder.Remove(selectBuilder.Length - 4, 4);

            DataTable dt;
            dt = Factory.GetFactory.ExecuteDataTable(selectBuilder.ToString());
            return dt;
        }
        public DataTable GetAllRecord_Datatable(string[] select, String[] where, params String[] orderBy)
        {
            Type tip = this.GetType();
            TablesAttribute tblAtr = ((TablesAttribute[])tip.GetCustomAttributes(typeof(TablesAttribute), false))[0];
            string tablename = tblAtr.Table_Name;
            StringBuilder selectBuilder = new StringBuilder();
            selectBuilder.Append("SELECT ");

            for (int i = 0; i < select.Length; i++)
            {
                selectBuilder.Append(select[i] + ",");
            }
            selectBuilder.Remove(selectBuilder.Length - 1, 1);
            selectBuilder.Append(" FROM " + tablename);

            if (where.Length > 0)
            {
                selectBuilder.Append(" WHERE ");
                foreach (String prp in where)
                {
                    selectBuilder.Append(prp + " and ");
                }
                selectBuilder.Remove(selectBuilder.Length - 4, 4);
            }

            selectBuilder.Append(" ORDER BY ");

            for (int i = 0; i < orderBy.Length; i++)
            {
                selectBuilder.Append(orderBy[i] + ",");
            }
            selectBuilder.Remove(selectBuilder.Length - 1, 1);

            DataTable dt;
            dt = Factory.GetFactory.ExecuteDataTable(selectBuilder.ToString());
            return dt;
        }

        public IDataReader GetAllRecord_Reader()
        {
            Type tip = this.GetType();
            TablesAttribute tblAtr = ((TablesAttribute[])tip.GetCustomAttributes(typeof(TablesAttribute), false))[0];
            string tablename = tblAtr.Table_Name;
            StringBuilder selectBuilder = new StringBuilder();
            selectBuilder.Append("SELECT ");

            foreach (PropertyInfo prp in tip.GetProperties())
            {
                FieldsAttribute atr = ((FieldsAttribute[])prp.GetCustomAttributes(typeof(FieldsAttribute), false))[0];

                string fieldname = atr.Fields_Name;
                if (atr.InSelect == true)
                {
                    selectBuilder.Append(fieldname + ",");
                }
            }

            selectBuilder.Remove(selectBuilder.Length - 1, 1);
            selectBuilder.Append(" FROM " + tablename);

            IDataReader rdr = Factory.GetFactory.ExecuteReader(selectBuilder.ToString());
            return rdr;
        }

        public object ConvertVal(SqlDbType tip, object value)
        {
            switch (tip)
            {
                case SqlDbType.Bit:
                    return Convert.ToBoolean(value);
                    break;
                case SqlDbType.DateTime:
                    return Convert.ToDateTime(value);
                    break;
                case SqlDbType.Decimal:
                    return Convert.ToDecimal(value);
                    break;
                case SqlDbType.Float:
                    return Convert.ToDecimal(value);
                    break;
                case SqlDbType.Int:
                    return Convert.ToInt32(value);
                    break;
                case SqlDbType.NChar:
                    return Convert.ToString(value);
                    break;
                case SqlDbType.NText:
                    return Convert.ToString(value);
                    break;
                case SqlDbType.NVarChar:
                    return Convert.ToString(value);
                    break;
                case SqlDbType.Text:
                    return Convert.ToString(value);
                    break;
                case SqlDbType.VarChar:
                    return Convert.ToString(value);
                    break;
                default:
                    return value;
                    break;
            }
        }

        public void TekKayitSil(string kolonName, string SilinecekDeger)
        {
            Type tip = this.GetType();
            TablesAttribute tblAtr = ((TablesAttribute[])tip.GetCustomAttributes(typeof(TablesAttribute), false))[0];
            string tablename = tblAtr.Table_Name;
            StringBuilder selectBuilder = new StringBuilder();
            selectBuilder.Append("DELETE ");
            selectBuilder.Append(" FROM " + tablename + " WHERE ");
            selectBuilder.Append(kolonName + " = " + "'" + SilinecekDeger + "'");
            Factory.GetFactory.ExecuteNonQuery(selectBuilder.ToString());
        }

        public void KayitBul(String[] select, params String[] where)
        {
            Type tip = this.GetType();
            TablesAttribute tblAtr = ((TablesAttribute[])tip.GetCustomAttributes(typeof(TablesAttribute), false))[0];
            string tablename = tblAtr.Table_Name;
            StringBuilder selectBuilder = new StringBuilder();
            selectBuilder.Append("SELECT ");
            List<String> alanlar = new List<string>();
            foreach (String prp in select)
            {
                selectBuilder.Append(prp + ",");
                alanlar.Add(prp);
            }
            selectBuilder.Remove(selectBuilder.Length - 1, 1);
            selectBuilder.Append(" FROM " + tablename + " WHERE ");
            foreach (String prp in where)
            {
                selectBuilder.Append(prp + " and ");
            }
            selectBuilder.Remove(selectBuilder.Length - 4, 4);
            IDataReader rd = Factory.GetFactory.ExecuteReader(selectBuilder.ToString());
            while (rd.Read())
            {
                foreach (PropertyInfo prp in tip.GetProperties())
                {
                    FieldsAttribute atr = ((FieldsAttribute[])prp.GetCustomAttributes(typeof(FieldsAttribute), false))[0];
                    string fieldname = atr.Fields_Name;
                    if (alanlar.Contains(fieldname) == true)
                        prp.SetValue(this, ConvertVal(atr.DbType, rd[fieldname]), null);
                }
            }
            rd.Close();
            rd.Dispose();
        }

        public void KayitBul(params String[] where)
        {
            Type tip = this.GetType();
            TablesAttribute tblAtr = ((TablesAttribute[])tip.GetCustomAttributes(typeof(TablesAttribute), false))[0];
            string tablename = tblAtr.Table_Name;
            StringBuilder selectBuilder = new StringBuilder();
            selectBuilder.Append("SELECT ");
            foreach (PropertyInfo prp in tip.GetProperties())
            {
                FieldsAttribute atr = ((FieldsAttribute[])prp.GetCustomAttributes(typeof(FieldsAttribute), false))[0];
                string fieldname = atr.Fields_Name;
                if (atr.InSelect == true)
                {
                    selectBuilder.Append(fieldname + ",");
                }
            }
            selectBuilder.Remove(selectBuilder.Length - 1, 1);
            selectBuilder.Append(" FROM " + tablename + " WHERE ");
            foreach (String prp in where)
            {
                selectBuilder.Append(prp + " and ");
            }
            selectBuilder.Remove(selectBuilder.Length - 4, 4);
            IDataReader rd = Factory.GetFactory.ExecuteReader(selectBuilder.ToString());
            while (rd.Read())
            {
                foreach (PropertyInfo prp in tip.GetProperties())
                {
                    FieldsAttribute atr = ((FieldsAttribute[])prp.GetCustomAttributes(typeof(FieldsAttribute), false))[0];

                    string fieldname = atr.Fields_Name;
                    if (atr.InSelect == true)
                    {
                        prp.SetValue(this, ConvertVal(atr.DbType, rd[fieldname]), null);
                    }
                }

            }
            rd.Close();
            rd.Dispose();
        }

        public void KayitBul()
        {
            Type tip = this.GetType();
            TablesAttribute tblAtr = ((TablesAttribute[])tip.GetCustomAttributes(typeof(TablesAttribute), false))[0];
            string tablename = tblAtr.Table_Name;
            StringBuilder selectBuilder = new StringBuilder();
            List<IDbDataParameter> prm = new List<IDbDataParameter>();
            selectBuilder.Append("SELECT ");
            foreach (PropertyInfo prp in tip.GetProperties())
            {
                FieldsAttribute atr = ((FieldsAttribute[])prp.GetCustomAttributes(typeof(FieldsAttribute), false))[0];
                string fieldname = atr.Fields_Name;
                if (atr.InSelect == true)
                {
                    selectBuilder.Append(fieldname + ",");
                }
            }
            selectBuilder.Remove(selectBuilder.Length - 1, 1);
            selectBuilder.Append(" FROM " + tablename + " WHERE ");
            foreach (PropertyInfo prp in tip.GetProperties())
            {
                FieldsAttribute atr = ((FieldsAttribute[])prp.GetCustomAttributes(typeof(FieldsAttribute), false))[0];
                string fieldname = atr.Fields_Name;
                object alanDegeri = prp.GetValue(this, null);
                if (atr.PrimaryKey == true && alanDegeri != null)
                {
                    selectBuilder.Append(fieldname + "=" + Factory.GetFactory.GetParameterToken() + fieldname + " and ");
                }
            }
            selectBuilder.Remove(selectBuilder.Length - 4, 4);
            foreach (PropertyInfo prp in tip.GetProperties())
            {
                FieldsAttribute atr = ((FieldsAttribute[])prp.GetCustomAttributes(typeof(FieldsAttribute), false))[0];
                object alanDegeri1 = prp.GetValue(this, null);
                if (atr.PrimaryKey == true && alanDegeri1 != null)
                {
                    prm.Add(GetParameter(atr.Fields_Name, alanDegeri1));
                }
            }
            IDataReader rd = Factory.GetFactory.ExecuteReader(CommandType.Text, selectBuilder.ToString(), prm.ToArray());
            while (rd.Read())
            {
                foreach (PropertyInfo prp in tip.GetProperties())
                {
                    FieldsAttribute atr = ((FieldsAttribute[])prp.GetCustomAttributes(typeof(FieldsAttribute), false))[0];

                    string fieldname = atr.Fields_Name;
                    if (atr.InSelect == true && rd[fieldname] != DBNull.Value)
                    {
                        prp.SetValue(this, ConvertVal(atr.DbType, rd[fieldname]), null);
                    }
                }

            }
            rd.Close();
            rd.Dispose();
        }

        public List<hakan> GetAllRecord_Model<hakan>()
        {
            Type tip = this.GetType();
            TablesAttribute tblAtr = ((TablesAttribute[])tip.GetCustomAttributes(typeof(TablesAttribute), false))[0];
            string tablename = tblAtr.Table_Name;
            StringBuilder selectBuilder = new StringBuilder();
            selectBuilder.Append("SELECT ");

            foreach (PropertyInfo prp in tip.GetProperties())
            {
                FieldsAttribute atr = ((FieldsAttribute[])prp.GetCustomAttributes(typeof(FieldsAttribute), false))[0];

                string fieldname = atr.Fields_Name;
                if (atr.InSelect == true)
                {
                    selectBuilder.Append(fieldname + ",");
                }
            }

            selectBuilder.Remove(selectBuilder.Length - 1, 1);
            selectBuilder.Append(" FROM " + tablename);

            List<hakan> lst = new List<hakan>();
            hakan createdObject;
            IDataReader rd = Factory.GetFactory.ExecuteReader(selectBuilder.ToString());
            while (rd.Read())
            {
                ConstructorInfo constructor = tip.GetConstructor(new Type[] { });
                createdObject = (hakan)constructor.Invoke(null);
                Type tp = createdObject.GetType();

                foreach (PropertyInfo prp in tp.GetProperties())
                {
                    FieldsAttribute atr = ((FieldsAttribute[])prp.GetCustomAttributes(typeof(FieldsAttribute), false))[0];

                    string fieldname = atr.Fields_Name;
                    if (atr.InSelect == true)
                    {
                        prp.SetValue(createdObject, ConvertVal(atr.DbType, rd[fieldname]), null);
                    }
                }

                lst.Add(createdObject);
            }
            rd.Close();
            rd.Dispose();
            return lst;
        }

        public List<cls> GetToRecord_Model<cls>(params string[] where)
        {
            Type tip = this.GetType();
            TablesAttribute tblAtr = ((TablesAttribute[])tip.GetCustomAttributes(typeof(TablesAttribute), false))[0];
            string tablename = tblAtr.Table_Name;
            StringBuilder selectBuilder = new StringBuilder();
            selectBuilder.Append("SELECT ");
            foreach (PropertyInfo prp in tip.GetProperties())
            {
                FieldsAttribute atr = ((FieldsAttribute[])prp.GetCustomAttributes(typeof(FieldsAttribute), false))[0];

                string fieldname = atr.Fields_Name;
                if (atr.InSelect == true)
                {
                    selectBuilder.Append(fieldname + ",");
                }
            }
            selectBuilder.Remove(selectBuilder.Length - 1, 1);
            selectBuilder.Append(" FROM " + tablename + " WHERE ");
            foreach (String prp in where)
            {
                selectBuilder.Append(prp + " and ");
            }
            selectBuilder.Remove(selectBuilder.Length - 4, 4);
            List<cls> lst = new List<cls>();
            cls createdObject;
            IDataReader rd = Factory.GetFactory.ExecuteReader(selectBuilder.ToString());
            while (rd.Read())
            {
                ConstructorInfo constructor = tip.GetConstructor(new Type[] { });
                createdObject = (cls)constructor.Invoke(null);
                Type tp = createdObject.GetType();
                foreach (PropertyInfo prp in tp.GetProperties())
                {
                    FieldsAttribute atr = ((FieldsAttribute[])prp.GetCustomAttributes(typeof(FieldsAttribute), false))[0];
                    string fieldname = atr.Fields_Name;
                    if (atr.InSelect == true)
                    {
                        prp.SetValue(createdObject, ConvertVal(atr.DbType, rd[fieldname]), null);
                    }
                }

                lst.Add(createdObject);
            }
            rd.Close();
            rd.Dispose();
            return lst;
        }

        public void KayitSil(params String[] where)
        {
            Type tip = this.GetType();
            TablesAttribute tblAtr = ((TablesAttribute[])tip.GetCustomAttributes(typeof(TablesAttribute), false))[0];
            string tablename = tblAtr.Table_Name;
            StringBuilder selectBuilder = new StringBuilder();
            selectBuilder.Append("DELETE ");
            selectBuilder.Append(" FROM " + tablename + " WHERE ");

            foreach (String prp in where)
            {
                selectBuilder.Append(prp + " and ");
            }
            selectBuilder.Remove(selectBuilder.Length - 4, 4);

            Factory.GetFactory.ExecuteNonQuery(selectBuilder.ToString());
        }

        public void KayitSil()
        {
            Type tip = this.GetType();
            TablesAttribute tblAtr = ((TablesAttribute[])tip.GetCustomAttributes(typeof(TablesAttribute), false))[0];
            string tablename = tblAtr.Table_Name;
            StringBuilder selectBuilder = new StringBuilder();
            selectBuilder.Append("DELETE ");
            selectBuilder.Append(" FROM " + tablename + " WHERE ");
            List<IDbDataParameter> prm = new List<IDbDataParameter>();
            bool var = false;
            foreach (PropertyInfo prp in tip.GetProperties())
            {
                FieldsAttribute atr = ((FieldsAttribute[])prp.GetCustomAttributes(typeof(FieldsAttribute), false))[0];
                string fieldname = atr.Fields_Name;
                object alanDegeri = prp.GetValue(this, null);

                if (atr.PrimaryKey == true)
                {
                    selectBuilder.Append(fieldname + "=" + Factory.GetFactory.GetParameterToken() + fieldname + " and ");
                    var = true;
                }
            }
            selectBuilder.Remove(selectBuilder.Length - 4, 4);

            foreach (PropertyInfo prp in tip.GetProperties())
            {
                FieldsAttribute atr = ((FieldsAttribute[])prp.GetCustomAttributes(typeof(FieldsAttribute), false))[0];
                if (atr.PrimaryKey == true && (prp.GetValue(this, null) != null))
                {
                    object alanDegeri = prp.GetValue(this, null);
                    prm.Add(GetParameter(atr.Fields_Name, alanDegeri));
                }
            }
            if (var == true)
                Factory.GetFactory.ExecuteNonQuery(CommandType.Text, selectBuilder.ToString(), prm.ToArray());
        }

        public void KayitSil(string quary)
        {
            Factory.GetFactory.ExecuteNonQuery(CommandType.Text, quary);
        }

        private DbType GetTip(string tip)
        {
            DbType reTip = DbType.String;
            switch (tip)
            {
                case "System.Binary":
                    reTip = DbType.Binary;
                    break;
                case "System.Boolean":
                    reTip = DbType.Boolean;
                    break;
                case "System.String":
                    reTip = DbType.String;
                    break;
                case "System.Date":
                    reTip = DbType.Date;
                    break;
                case "System.DateTime":
                    reTip = DbType.DateTime;
                    break;
                case "System.DateTime2":
                    reTip = DbType.DateTime2;
                    break;
                case "System.Decimal":
                    reTip = DbType.Decimal;
                    break;
                case "System.Single":
                    reTip = DbType.Single;
                    break;
                case "System.Int32":
                    reTip = DbType.Int32;
                    break;
                case "System.Int16":
                    reTip = DbType.Int16;
                    break;
                default:
                    break;
            }
            return reTip;
        }

        private IDbDataParameter GetParameter(string paramName, Object value)
        {
            DbType tip = GetTip(value.GetType().ToString());
            IDbDataParameter prm = null;
            switch (tip)
            {
                case DbType.Binary:
                    prm = Factory.GetFactory.GetParameter<byte[]>(paramName, tip, (byte[])value);
                    break;
                case DbType.Boolean:
                    prm = Factory.GetFactory.GetParameter<bool>(paramName, tip, (bool)value);
                    break;
                case DbType.String:
                    prm = Factory.GetFactory.GetParameter<string>(paramName, tip, (string)value);
                    break;
                case DbType.Date:
                    prm = Factory.GetFactory.GetParameter<DateTime>(paramName, tip, (DateTime)value);
                    break;
                case DbType.DateTime:
                    prm = Factory.GetFactory.GetParameter<DateTime>(paramName, tip, (DateTime)value);
                    break;
                case DbType.DateTime2:
                    prm = Factory.GetFactory.GetParameter<DateTime>(paramName, tip, (DateTime)value);
                    break;
                case DbType.Decimal:
                    prm = Factory.GetFactory.GetParameter<decimal>(paramName, tip, (decimal)value);
                    break;
                case DbType.Single:
                    prm = Factory.GetFactory.GetParameter<decimal>(paramName, tip, (decimal)Convert.ToDecimal(value));
                    break;
                case DbType.Int32:
                    prm = Factory.GetFactory.GetParameter<int>(paramName, tip, (int)value);
                    break;
                case DbType.Int16:
                    prm = Factory.GetFactory.GetParameter<Int16>(paramName, tip, (Int16)value);
                    break;
                default:
                    break;
            }
            return prm;
        }

        public Boolean recordExits()
        {
            Type tip = this.GetType();
            TablesAttribute tblAtr = ((TablesAttribute[])tip.GetCustomAttributes(typeof(TablesAttribute), false))[0];
            string tablename = tblAtr.Table_Name;
            StringBuilder selectBuilder = new StringBuilder();
            selectBuilder.Append("SELECT COUNT(*) ");
            selectBuilder.Append(" FROM " + tablename + " WHERE ");
            List<IDbDataParameter> prm = new List<IDbDataParameter>();
            foreach (PropertyInfo prp in tip.GetProperties())
            {
                FieldsAttribute atr = ((FieldsAttribute[])prp.GetCustomAttributes(typeof(FieldsAttribute), false))[0];
                string fieldname = atr.Fields_Name;
                object alanDegeri = prp.GetValue(this, null);

                if (atr.PrimaryKey == true && alanDegeri != null)
                {
                    prm.Add(GetParameter(atr.Fields_Name, alanDegeri));
                    selectBuilder.Append(fieldname + "=" + Factory.GetFactory.GetParameterToken() + fieldname + " and ");
                }
            }
            selectBuilder.Remove(selectBuilder.Length - 4, 4);

            IDataReader rd = Factory.GetFactory.ExecuteReader(CommandType.Text, selectBuilder.ToString(), prm.ToArray());
            Boolean sonuc = false;
            while (rd.Read())
            {
                if (rd.GetInt32(0) > 0)
                    sonuc = true;
            }
            rd.Close();
            rd.Dispose();
            return sonuc;
        }

        public void Save()
        {
            Type tip = this.GetType();
            TablesAttribute tblAtr = ((TablesAttribute[])tip.GetCustomAttributes(typeof(TablesAttribute), false))[0];
            string tablename = tblAtr.Table_Name;
            StringBuilder selectBuilder = new StringBuilder();
            StringBuilder insertBuilder1 = new StringBuilder();
            StringBuilder insertBuilder2 = new StringBuilder();
            StringBuilder updateBuilder = new StringBuilder();

            bool ilk = true;
            List<IDbDataParameter> prm = new List<IDbDataParameter>();
            foreach (PropertyInfo prp in tip.GetProperties())
            {
                FieldsAttribute atr = ((FieldsAttribute[])prp.GetCustomAttributes(typeof(FieldsAttribute), false))[0];
                string fieldname = atr.Fields_Name;
                object alanDegeri = prp.GetValue(this, null);

                if (alanDegeri != null && atr.DbType == SqlDbType.DateTime)
                {
                    if (Convert.ToDateTime(alanDegeri).Year == 1)
                    {
                        alanDegeri = new DateTime(1900, 1, 1, 0, 0, 0);
                    }
                }

                if (ilk == true)
                {
                    ilk = false;
                    selectBuilder.Append("IF EXISTS(SELECT " + atr.Fields_Name);
                    selectBuilder.Append(" FROM " + tablename + " WHERE (");
                    updateBuilder.Append(" UPDATE " + tablename + " set ");
                    insertBuilder1.Append(" INSERT INTO " + tablename + " (");
                    insertBuilder2.Append(" VALUES (");
                }

                if (atr.InInsert == true && alanDegeri != null)
                {
                    prm.Add(GetParameter(atr.Fields_Name, alanDegeri));
                    if (atr.PrimaryKey == false && atr.Identity == false)
                    {
                        updateBuilder.Append(atr.Fields_Name + "=" + Factory.GetFactory.GetParameterToken() + atr.Fields_Name + ",");
                    }
                    if (atr.Identity == false)
                    {
                        insertBuilder1.Append(atr.Fields_Name + ",");
                        insertBuilder2.Append(Factory.GetFactory.GetParameterToken() + atr.Fields_Name + ",");
                    }
                }

                if (atr.PrimaryKey == true && alanDegeri != null)
                {
                    selectBuilder.Append(fieldname + "=" + Factory.GetFactory.GetParameterToken() + fieldname + " and ");
                }
            }

            insertBuilder1.Remove(insertBuilder1.Length - 1, 1);
            insertBuilder1.Append(") ");

            insertBuilder2.Remove(insertBuilder2.Length - 1, 1);
            insertBuilder2.Append(") ");

            updateBuilder.Remove(updateBuilder.Length - 1, 1);
            selectBuilder.Remove(selectBuilder.Length - 4, 4);
            selectBuilder.Append(")");

            updateBuilder.Append(" " + selectBuilder.ToString().Substring(selectBuilder.ToString().IndexOf("WHERE")) + " ELSE ");
            selectBuilder.Append(")");

            string execSql = selectBuilder.ToString() + updateBuilder.ToString() + insertBuilder1.ToString() + insertBuilder2.ToString();

            Factory.GetFactory.ExecuteNonQuery(CommandType.Text, execSql, prm.ToArray());

        }

        public void UpdateWithPrimarykey()
        {
            Type tip = this.GetType();
            TablesAttribute tblAtr = ((TablesAttribute[])tip.GetCustomAttributes(typeof(TablesAttribute), false))[0];
            string tablename = tblAtr.Table_Name;
            StringBuilder updateBuilder = new StringBuilder();
            updateBuilder.Append("UPDATE " + tablename + " SET ");
            bool var = false;//update edilecek alan var mı?
            List<IDbDataParameter> prm = new List<IDbDataParameter>();
            foreach (PropertyInfo prp in tip.GetProperties())
            {
                FieldsAttribute atr = ((FieldsAttribute[])prp.GetCustomAttributes(typeof(FieldsAttribute), false))[0];
                string fieldname = atr.Fields_Name;
                if (atr.PrimaryKey == false && prp.GetValue(this, null) != null && atr.InUpdate == true)
                {
                    updateBuilder.Append(fieldname + "=" + Factory.GetFactory.GetParameterToken() + fieldname + ",");
                    var = true;
                }
            }

            updateBuilder.Remove(updateBuilder.Length - 1, 1);
            updateBuilder.Append(" WHERE ");

            foreach (PropertyInfo prp in tip.GetProperties())
            {
                FieldsAttribute atr = ((FieldsAttribute[])prp.GetCustomAttributes(typeof(FieldsAttribute), false))[0];
                string fieldname = atr.Fields_Name;
                if (atr.PrimaryKey == true)
                    updateBuilder.Append(fieldname + "=" + Factory.GetFactory.GetParameterToken() + fieldname + " and ");
            }
            updateBuilder.Remove(updateBuilder.Length - 4, 4);
            foreach (PropertyInfo prp in tip.GetProperties())
            {
                FieldsAttribute atr = ((FieldsAttribute[])prp.GetCustomAttributes(typeof(FieldsAttribute), false))[0];
                if (atr.PrimaryKey == true || (prp.GetValue(this, null) != null && atr.InUpdate == true))
                {
                    object alanDegeri = prp.GetValue(this, null);
                    prm.Add(GetParameter(atr.Fields_Name, alanDegeri));
                }
            }

            if (var == true)
            {
                Factory.GetFactory.ExecuteNonQuery(CommandType.Text, updateBuilder.ToString(), prm.ToArray());
            }
        }

        public void GetInsert_Collections()
        {
            Type tip = this.GetType();
            TablesAttribute tblAtr = ((TablesAttribute[])tip.GetCustomAttributes(typeof(TablesAttribute), false))[0];
            String tabloAdi = tblAtr.Table_Name;
            String token = Factory.GetFactory.GetParameterToken();
            StringBuilder insertBuilder = new StringBuilder();
            List<IDbDataParameter> prm = new List<IDbDataParameter>();
            bool varr = false;
            insertBuilder.Append("Insert into ");
            insertBuilder.Append(tabloAdi);
            insertBuilder.Append(" (");
            foreach (PropertyInfo prp in tip.GetProperties())
            {
                FieldsAttribute atr = ((FieldsAttribute[])prp.GetCustomAttributes(typeof(FieldsAttribute), false))[0];
                object deger = prp.GetValue(this, null);

                if (atr.Identity == false && atr.InInsert == true && deger != null)
                {
                    varr = true;
                    insertBuilder.Append(atr.Fields_Name);
                    insertBuilder.Append(",");
                }
            }
            insertBuilder.Remove(insertBuilder.Length - 1, 1);
            insertBuilder.Append(") Values (");
            foreach (PropertyInfo prp in tip.GetProperties())
            {
                FieldsAttribute atr = ((FieldsAttribute[])prp.GetCustomAttributes(typeof(FieldsAttribute), false))[0];
                object deger = prp.GetValue(this, null);
                if (!atr.Identity && atr.InInsert == true && deger != null)
                {
                    insertBuilder.Append(token + atr.Fields_Name);
                    insertBuilder.Append(",");
                }
            }
            insertBuilder.Remove(insertBuilder.Length - 1, 1);
            insertBuilder.Append(")");
            foreach (PropertyInfo prp in tip.GetProperties())
            {
                FieldsAttribute atr = ((FieldsAttribute[])prp.GetCustomAttributes(typeof(FieldsAttribute), false))[0];
                object alanDegeri = prp.GetValue(this, null);
                if (atr.Identity == false && atr.InInsert == true && alanDegeri != null)
                {
                    prm.Add(GetParameter(atr.Fields_Name, alanDegeri));
                }
            }
            if (varr)
                Factory.GetFactory.ExecuteNonQuery(CommandType.Text, insertBuilder.ToString(), prm.ToArray());
        }

    }
}
